package com.example.orderfood2.util;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.example.orderfood2.entity.Student;
import com.example.orderfood2.entity.Teacher;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
//import android.database.CursorFactory;

//连接数据库
public class DBUtil extends SQLiteOpenHelper {
    //数据库名字
    private static final String DATABASE_NAME = "Mysql.db";
    //    数据库版本
    private static final int DATABASE_VERSION = 2;
    private Context context; // 添加Context成员变量

    //创建数据库并初始化
    public DBUtil(Context context) {
//        而在这里是：在子类构造方法中，要调用父类构造方法，即是继承了SQLiteOpenHelper的DBUtil，要求4个参数
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        this.context = context; // 初始化context
    }


    @Override
    public void onCreate(SQLiteDatabase db) {
        //创建manager表的SQL语句
        String createManagerTableQuery = "CREATE TABLE manager(" +
                "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                "username TEXT NOT NULL," +
                "password TEXT NOT NULL" +
                ")";
        // 执行创建manager表的语句
        db.execSQL(createManagerTableQuery);

        String createTeacherTableQuery = "CREATE TABLE teacher(" +
                "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                "username TEXT NOT NULL," +
                "password TEXT NOT NULL," +
                "sex TEXT NOT NULL," +
                "title TEXT NOT NULL," +
                "date TEXT NOT NULL," +
                "science TEXT NOT NULL" +
                ")";
        db.execSQL(createTeacherTableQuery);

        String createStudentTableQuery = "CREATE TABLE student (" +
                "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                "username TEXT NOT NULL," +
                "password TEXT NOT NULL," +
                "sex TEXT NOT NULL," +
                "classId TEXT NOT NULL," +
                "score TEXT NOT NULL," +
                "date TEXT NOT NULL," +
                "enrol TEXT NOT NULL" +
                ")";
        db.execSQL(createStudentTableQuery);

        // 新增创建class_info表的SQL语句
        String createClassInfoTableQuery = "CREATE TABLE class_info(" +
                "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                "classnumber TEXT NOT NULL," +
                "classname TEXT NOT NULL" +
                ")";
        db.execSQL(createClassInfoTableQuery);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int i, int i1) {
// 当数据库版本更新时，可以在这里编写更新表结构的逻辑
        // 例如，删除旧表并重新创建
        db.execSQL("DROP TABLE IF EXISTS manager");
        db.execSQL("DROP TABLE IF EXISTS teacher");
        db.execSQL("DROP TABLE IF EXISTS student");
        db.execSQL("DROP TABLE IF EXISTS class_info"); // 添加删除class_info表的语句
        onCreate(db);
    }

    //    teacher表的增删改查函数
    //teacher找到对应的学生
    public List<Student> getStudentsByTeacherScience(String science){
        List<Student> studentList=new ArrayList<>();
        SQLiteDatabase db=getWritableDatabase();
        // 先根据science获取classnumber
        Cursor classCursor=db.rawQuery("SELECT classnumber FROM class_info WHERE classname =?",new String[]{science});
        while (classCursor.moveToNext()){
            String classnumber=classCursor.getString(0);
            // 再根据classnumber获取学生信息
            Cursor studentCursor = db.query("student", null, "classId =?", new String[]{classnumber}, null, null, null);
            if(studentCursor!=null){
                try {
                    while (studentCursor.moveToNext()) {
                        int id = studentCursor.getInt(0);
                        String username = studentCursor.getString(1);
                        String password = studentCursor.getString(2);
                        String sex = studentCursor.getString(3);
                        String classId = studentCursor.getString(4);
                        String score = studentCursor.getString(5);
                        String date = studentCursor.getString(6);
                        String enrol = studentCursor.getString(7);
                        Student student = new Student(id, username, password, sex, classId, score, date, enrol);
                        studentList.add(student);
                    }
                } finally {
                    studentCursor.close();
                }
            }
        }
        if (classCursor != null) {
            classCursor.close();
        }
        return studentList;
    }
//    teacher的密码修改
    public int TeacherChangePassword(String username,String password){
        SQLiteDatabase db=getWritableDatabase();
        ContentValues T=new ContentValues();
        T.put("password",password);
        int teacher=db.update("teacher",T,"username like ?",new String[]{username});
        return teacher;
    }

    //    teacher表的id,username的查
    public Cursor getAllTeachers(){
        SQLiteDatabase db=this.getWritableDatabase();
        return db.rawQuery("SELECT id, username FROM teacher",null);
    }
//    插入
    public long registerTeacher(Teacher teacher) {
        SQLiteDatabase db = getWritableDatabase();
        ContentValues T = new ContentValues();
        T.put("username", teacher.getUsername());
        T.put("password", teacher.getPassword());
        T.put("sex", teacher.getSex());
        T.put("title", teacher.getTitle());
        T.put("date", teacher.getDate());
        T.put("science", teacher.getScience());
        //返回-1则失败，否则成功
        return db.insert("teacher", null, T);
    }

    //    查找
    public boolean loginTeacher(String username, String password) {
        SQLiteDatabase db1 = getWritableDatabase();
        boolean result = false;
//        从teacher表中查找限定条件为"username like ?"的元素，并组和为数组
        Cursor usernames = db1.query("teacher", null, "username like ?", new String[]{username}, null, null, null);
//        进行判断和密码验证
        if (usernames != null) {
            try {
                while (usernames.moveToNext()) {
//                将usernames中的第2列(即密码列)拿出来
                    String password1 = usernames.getString(2);
                    if (password1.equals(password)) {
                        result = true;
                        break;
                    }
                }
            } finally {
                usernames.close();
            }
        }
        return result;
    }

//    teacher的删除
    public boolean deleteTeacher(int id){
        SQLiteDatabase db=this.getWritableDatabase();
        return db.delete("teacher","id=?",new String[]{String.valueOf(id)})>0;
    }
//    teacher表的修改
    public boolean updateTeacher(int id,Teacher teacher){
        SQLiteDatabase db = getWritableDatabase();
        ContentValues T = new ContentValues();
        T.put("username", teacher.getUsername());
        T.put("password", teacher.getPassword());
        T.put("sex", teacher.getSex());
        T.put("title", teacher.getTitle());
        T.put("date", teacher.getDate());
        T.put("science", teacher.getScience());
        return db.update("teacher",T,"id=?",new String[]{String.valueOf(id)})>0;
    }

    //    manager表的查
    public boolean loginManager(String username, String password) {
        SQLiteDatabase db2 = getWritableDatabase();
        boolean result = false;
        Cursor usernames = db2.query("manager", null, "username like ?", new String[]{username}, null, null, null);

        if (usernames != null) {
            try {
                while (usernames.moveToNext()) {
                    String password1 = usernames.getString(2);
                    if (password1.equals(password)) {
                        result = true;
                        break;
                    }
                }
            } finally {
                usernames.close();
            }
        }
        return result;
    }

//    注册manager
    public long registerManager(String username,String password){
        SQLiteDatabase db=getWritableDatabase();
        ContentValues M=new ContentValues();
        M.put("username",username);
        M.put("password",password);
        return db.insert("manager",null,M);
    }
    //    manager修改密码函数
    public int ManagerChangePassword(String username, String password) {
        SQLiteDatabase db3 = getWritableDatabase();
//        创建容器来打包数据
        ContentValues values = new ContentValues();
        values.put("username", username);
        values.put("password", password);
        int manager = db3.update("manager", values, "username like ?", new String[]{username});
//        主要不是-1则成功
        return manager;
    }

    //    manager注销用户
    public void DeleteManager(String username) {
        SQLiteDatabase db4 = getWritableDatabase();
        db4.delete("manager", "username =?", new String[]{username});
    }
    //    student表id,username的查
    public Cursor getAllStudents(){
        SQLiteDatabase db=this.getWritableDatabase();
        return db.rawQuery("SELECT id, username FROM student",null);
    }

    //    通过id来查找teacher表
    public Teacher getTeacherById(int id){
        SQLiteDatabase db=getWritableDatabase();
        Cursor cursor=db.query("teacher",
                null,
                "id=?",
                new String[]{String.valueOf(id)},
                null, null, null);
        if(cursor!=null&&cursor.moveToFirst()){
            Teacher teacher = new Teacher(
                    cursor.getInt(0),
                    cursor.getString(1),
                    cursor.getString(2),
                    cursor.getString(3),
                    cursor.getString(4),
                    cursor.getString(5),
                    cursor.getString(6));
            cursor.close();
            return teacher;
        };
        return null;
    }
    //通过username来查询teacher表
    public Teacher getTeacherByUsername(String username){
        SQLiteDatabase db=getWritableDatabase();
        Cursor cursor=db.query("teacher",null,"username=?",new String[]{username},null,null,null);
        if(cursor!=null&&cursor.moveToFirst()){
            Teacher teacher=new Teacher(
                    cursor.getInt(0),
                    cursor.getString(1),
                    cursor.getString(2),
                    cursor.getString(3),
                    cursor.getString(4),
                    cursor.getString(5),
                    cursor.getString(6));
            cursor.close();
            return teacher;
        };
        return null;
    }
    //    student表的查
    public boolean loginStudent(String username, String password) {
        SQLiteDatabase db3 = getWritableDatabase();
        boolean result = false;
        Cursor usernames = db3.query("student", null, "username like ?", new String[]{username}, null, null, null);
        if (usernames != null) {
            try {
                while (usernames.moveToNext()) {
                    String password1 = usernames.getString(2);
                    if (password1.equals(password)) {
                        result = true;
                        break;
                    }
                }
            } finally {
                usernames.close();
            }
        }
        return result;
    }
//    student修改密码
    public  int StudentChangePassword(String username,String password){
        SQLiteDatabase db=getWritableDatabase();
        ContentValues S=new ContentValues();
        S.put("password",password);
        int Student=db.update("student",S,"username=?",new String[]{username});
        return Student;
    }

    //通过id查询student
    public Student getStudentById(int id) {
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.query("student",
                null,
                "id=?",
                new String[]{String.valueOf(id)},
                null, null, null);

        if (cursor != null && cursor.moveToFirst()) {
            Student student = new Student(
                    cursor.getInt(0),
                    cursor.getString(1),
                    cursor.getString(2),
                    cursor.getString(3),
                    cursor.getString(4),
                    cursor.getString(5),
                    cursor.getString(6),
                    cursor.getString(7)
            );
            cursor.close();
            return student;
        }
        return null;
    }
    //student表中的生日日期
    public String getStudentDate(String account){
        SQLiteDatabase db = getWritableDatabase();
        Cursor cursor = null;
        String date = null;
        try {
            cursor = db.query("student", null, "username=?", new String[]{account}, null, null, null);
            if (cursor != null && cursor.moveToFirst()) {
                date = cursor.getString(6); // date 是第6列
            }
        } finally {
            if (cursor != null) {
                cursor.close();
            }
        }
        return date;
    }
    // student表的更新
    public boolean updateStudent(int id, Student student) {
        SQLiteDatabase db = getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("username", student.getUsername());
        values.put("password", student.getPassword());
        values.put("sex", student.getSex());
        values.put("classId", student.getClassId());
        values.put("score", student.getScore());
        values.put("date", student.getDate());
        values.put("enrol", student.getEnrol());
        return db.update("student", values, "id=?", new String[]{String.valueOf(id)}) > 0;
    }
//    student成绩排行
    public List<Student> getStudentsByScienceOrderByScoreDesc(String science){
        List<Student> studentList=new ArrayList<>();
        SQLiteDatabase db=getWritableDatabase();
        // 1. 查询 class_info 表，获取 classname = science 的所有 classnumber
        Cursor classCursor = db.rawQuery(
                "SELECT classnumber FROM class_info WHERE classname = ?",
                new String[]{science}
        );
        if(classCursor!=null){
            try{
                while (classCursor.moveToNext()){
                    String classnumber=classCursor.getString(0);
                    // 2. 查询 student 表，获取 classId = classnumber 的所有学生，并按 score 降序排序
                    Cursor studentCursor=db.rawQuery("SELECT * FROM student WHERE classId = ? ORDER BY score DESC",new String[]{classnumber});
                    if(studentCursor!=null){
                        try {
                            while (studentCursor. moveToNext()){
                                Student student = new Student(
                                        studentCursor.getInt(0),    // id
                                        studentCursor.getString(1), // username
                                        studentCursor.getString(2), // password
                                        studentCursor.getString(3), // sex
                                        studentCursor.getString(4), // classId
                                        studentCursor.getString(5), // score
                                        studentCursor.getString(6), // date
                                        studentCursor.getString(7)  // enrol
                                );
                                studentList.add(student);
                            }
                        }finally {
                            studentCursor.close();
                        }
                    }
                }
            }finally {
                classCursor.close();
            }
        }
        return studentList;
    }
    //    student表的添加
    public long registerStudent(Student student){
        SQLiteDatabase db=getWritableDatabase();
        ContentValues S=new ContentValues();
        S.put("username",student.getUsername());
        S.put("password",student.getPassword());
        S.put("sex",student.getSex());
        S.put("classId",student.getClassId());
        S.put("score",student.getScore());
        S.put("date",student.getDate());
        S.put("enrol",student.getEnrol());
        return db.insert("student",null,S);
    }
    //    student表的删除
    public boolean deleteStudent(int id){
        SQLiteDatabase db=this.getWritableDatabase();
        return db.delete("student","id=?",new String[]{String.valueOf(id)})>0;
    }

    //    class_info的添加
    public long InsertClass(String classNumber, String className) {
        SQLiteDatabase db5 = getWritableDatabase();
        ContentValues class_info = new ContentValues();
        class_info.put("classnumber", classNumber);
        class_info.put("classname", className);
        long result = db5.insert("class_info", null, class_info);
//        插入操作失败，该方法会返回 -1
        return result;
    }

    //    管理员的class_info的查询
    public List<String> QueryClassInfo() {
        List<String> classNumbers = new ArrayList<>();
        SQLiteDatabase db6 = getWritableDatabase();
        Cursor classInfo = null;
        try {
            classInfo = db6.query("class_info", null, null, null, null, null, null);
            if(classInfo!=null){
                while (classInfo.moveToNext()){
                    String number=classInfo.getString(1);
                    classNumbers.add(number);
                }
            }
        } catch (Exception e) {
            Log.e("DBUtil", "查询班级信息失败", e);
        } finally {
            if (classInfo != null) {
                classInfo.close();
            }
            db6.close();
        }
        return classNumbers;
    }

    //老师的class_info的查询
    public List<String> TeacherQueryClassInfo(String science){
        List<String> classNumbers=new ArrayList<>();
        Cursor classInfo=null;
        SQLiteDatabase db6 = getWritableDatabase();
        try {
            classInfo = db6.query("class_info", null, "classname=?", new String[]{science}, null, null, null);
            if(classInfo!=null){
                while (classInfo.moveToNext()){
                    String number=classInfo.getString(1);
                    classNumbers.add(number);
                }
            }
        } catch (Exception e) {
            Log.e("DBUtil", "查询班级信息失败", e);
        } finally {
            if (classInfo != null) {
                classInfo.close();
            }
            db6.close();
        }
        return classNumbers;
    }
    //    class_info的全部查询
//    rawQuery 是 Android SQLite 数据库中的一个核心方法，用于直接执行 SQL 查询语句并返回结果
    //参数：
    //sql：要执行的 SQL 查询语句（例如 SELECT * FROM table）。
    //selectionArgs：SQL 语句中占位符 ? 的实际参数值（可为 null）。
    //返回值：Cursor 对象，用于遍历查询结果。
    public Cursor getAllClasses(){
        SQLiteDatabase db=this.getWritableDatabase();
        return db.rawQuery("SELECT id, classnumber FROM class_info",null);
    }
//    class_info的删除
    public boolean deleteClass(int id) {
        SQLiteDatabase db = this.getWritableDatabase();
        return db.delete("class_info", "id=?", new String[]{String.valueOf(id)}) > 0;
    }
    //通过id查询具体的class
    public Cursor getClassById(int id) {
        SQLiteDatabase db = this.getReadableDatabase();
        return db.rawQuery("SELECT * FROM class_info WHERE id=?", new String[]{String.valueOf(id)});
    }
//    class_info的修改
public boolean updateClass(int id, String newNumber, String newName) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put("classnumber", newNumber);
    values.put("classname", newName);
    return db.update("class_info", values, "id=?", new String[]{String.valueOf(id)}) > 0;
}
}
